package com.fyy.execl.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author fyy
 * @Description execl导出及解析工具
 * @Date 2020/12/18 14:54
 */
@Component
public class ExcelUtil {

    private Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public List<String[]> analExcelData(MultipartFile file) throws IOException {

        if(!checkFile(file)){
            logger.error("文件解析错误");
            return null;
        }

        Workbook workbook = getWorkBook(file); //获得Workbook工作薄对象
        List<String[]> list = new ArrayList<String[]>(); //返回对象,每一行的值作为一个数组,所有的行作为一个集合
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                Sheet sheet = workbook.getSheetAt(sheetNum); //获得当前sheet工作表
                if(null == sheet){
                    continue;
                }
                int firstRowNum = sheet.getFirstRowNum(); //获取开始行
                int lastRowNum = sheet.getLastRowNum(); //获取结束行
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                    Row row = sheet.getRow(rowNum); //获取当前行
                    if(row == null){
                        continue;
                    }
                    int firstCellNum = row.getFirstCellNum(); //获得当前行开始列
                    int lastCellNum = row.getLastCellNum(); //获得当前行结束列
                    String[] cells = new String[row.getLastCellNum()];
                    for(int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++){ //循环数据
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    list.add(cells);
                }
            }
        }
        return list;
    }

    /**
     * 文件类型解析
     * @param file
     */
    public boolean checkFile(MultipartFile file) throws IOException {
        if(null == file){
            logger.error("文件不存在");
            return false;
        }
        String fileName = file.getOriginalFilename();
        if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
            logger.error(fileName + "不是execl文件");
            return false;
        }
        return true;
    }

    /**
     * 文件数据解析
     * @param file
     */
    public Workbook getWorkBook(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            if(fileName.endsWith("xls")){
                workbook = new HSSFWorkbook(is);
            }
            if(fileName.endsWith("xlsx")){
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            logger.error("文件数据解析错误[Workbook]:{}",e.getMessage());
        }
        return workbook;
    }

    /**
     * 文件数据类型解析
     * @param cell
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if(null == cell){
            return cellValue;
        }

        switch(cell.getCellTypeEnum()){
            case NUMERIC: //数字日期
                cellValue = stringDateProcess(cell);
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:
                cellValue = "";
                break;
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "位置类型";
                break;
        }
        return cellValue;
    }

    /**
     * 时间格式化处理
     * @param cell
     */
    public static String stringDateProcess(Cell cell) {
        String result = new String();
        if(HSSFDateUtil.isCellDateFormatted(cell)){
            SimpleDateFormat sdf = null;
            if(cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")){
                sdf = new SimpleDateFormat("HH:mm");
            }else{
                sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        }
        if(cell.getCellStyle().getDataFormat() == 58){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            result = sdf.format(date);
        }else{
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }
        return result;
    }


    @SuppressWarnings("resource")
    public void exportExcel(String filename, String[] titles, List<LinkedHashMap<String, Object>> data,
                            CellType[] styles, HttpServletResponse response) throws IOException {
        response.reset(); //清空输出流
        //设置下载的文件名
        response.setCharacterEncoding("utf-8");
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename + ".xls", "utf-8"));
        response.flushBuffer();

        OutputStream os = response.getOutputStream(); //取得输出流
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1"); //创建新的sheet
        HSSFCellStyle titleStyle = workbook.createCellStyle(); //表头
        HSSFFont titleFont = workbook.createFont(); //表头字体
        setTitleCellStyle(titleStyle, titleFont);

        HSSFCellStyle contentStyle = workbook.createCellStyle();// 内容样式
        HSSFFont contentFont = workbook.createFont();// 内容字体
        setContentCellStyle(contentStyle, contentFont);

        HSSFRow rowTitle = sheet.createRow((short) 0); //titles行
        for (int i = 0; i < titles.length; i++) {
            HSSFCell cell = rowTitle.createCell((short) i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(titleStyle);
        }

        List<LinkedHashMap<String, Object>> datas = data; //数据加载
        for (int i = 0; i < datas.size(); i++) {
            HSSFRow row = sheet.createRow((i + 1));
            Map<String, Object> map = datas.get(i);
            Iterator<Map.Entry<String, Object>> iter = map.entrySet().iterator();
            int j = 0;
            while (iter.hasNext()) {
                Map.Entry<String, Object> entry = (Map.Entry<String, Object>) iter.next();
                Object val = entry.getValue();

                HSSFCell cell = row.createCell((short) j);
                setCellType(cell, styles[j], val);
                cell.setCellStyle(contentStyle);
                j++;
            }
        }
        workbook.write(os);
    }

    /**
     * 设置单元格样式
     * @param cell
     * @param i
     * @param val
     */
    public static void setCellType(HSSFCell cell, CellType i, Object val) {
        if (i == CellType.NUMERIC) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(val.toString()));
        } else if (i == CellType.STRING) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(val.toString());
        } else if (i == CellType.BLANK) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue("");
        }

    }


    /**
     * 设置标题样式
     * @param style
     * @param font
     */
    public static void setTitleCellStyle(HSSFCellStyle style, HSSFFont font) {
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

        // 背景色
        // style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());前景色
        // style.setFillPattern(FillPatternType.BIG_SPOTS);
        style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());

        // 设置边框
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.MEDIUM);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setWrapText(true);// 自动换行

        font.setFontHeightInPoints((short) 10);
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setBold(true);
        font.setFontName("宋体");
        style.setFont(font);// 把字体 应用到当前样式
    }


    /**
     * 设置内容样式
     * @param contentstyle
     * @param font
     */
    public static void setContentCellStyle(HSSFCellStyle contentstyle, HSSFFont font) {
        // 设置边框
        contentstyle.setBorderBottom(BorderStyle.THIN);
        contentstyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        contentstyle.setBorderLeft(BorderStyle.THIN);
        contentstyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        contentstyle.setBorderRight(BorderStyle.THIN);
        contentstyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        contentstyle.setBorderTop(BorderStyle.THIN);
        contentstyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        font.setFontHeightInPoints((short) 10);
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setBold(false);
        font.setFontName("宋体");
        contentstyle.setFont(font);// 把字体 应用到当前样式
    }

}
